<?php

// 
// VERY DUMMY TEST CONTROLLER
// FOR THE SAKE OF THE EXAMPLE
// TEST IT AS http : / / <yourapplicationurl> / index.php ? r=test/test

class ReportController extends Controller {

    // no layouts here
//    public $layout = '';
    public $layout = '//layouts/eastsouthwest';

    public function actionReport1Old() {
//        $model= new Registrationmaster('search');
        $registrationmasters = Registrationmaster::model()->findAll();


        $title = "report1";
        //
        // get a reference to the path of PHPExcel classes 
        $phpExcelPath = Yii::getPathOfAlias('ext.phpexcel.Classes');

        // Turn off our amazing library autoload 
        spl_autoload_unregister(array('YiiBase', 'autoload'));

        //
        // making use of our reference, include the main class
        // when we do this, phpExcel has its own autoload registration
        // procedure (PHPExcel_Autoloader::Register();)
        include($phpExcelPath . DIRECTORY_SEPARATOR . 'PHPExcel.php');

        // Create new PHPExcel object
        $objPHPExcel = new PHPExcel();

        // Set properties
        $objPHPExcel->getProperties()->setCreator("ICTSoft")
                ->setLastModifiedBy("ICTSoft")
                ->setTitle($title)
                ->setSubject("ICTSoft Document")
                ->setDescription("xls generated using PHP classes.")
                ->setKeywords("xls php")
                ->setCategory("ICTSoft");

        // Add some data
//        $objPHPExcel->setActiveSheetIndex(0)
//                ->setCellValue('A1', 'Hello')
//                ->setCellValue('B2', 'world!')
//                ->setCellValue('C1', 'Hello')
//                ->setCellValue('D2', 'world!');
//
//        // Miscellaneous glyphs, UTF-8
//        $objPHPExcel->setActiveSheetIndex(0)
//                ->setCellValue('A4', 'Miscellaneous glyphs')
//                ->setCellValue('A5', 'éàèùâêîôûëïüÿäöüç');

        foreach ($registrationmasters as $key => $value) {
//            echo $key . ' \\ ' . $value->idregistrationmaster . ' -- ';
            $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A' . ($key + 1), $value->idregistrationmaster);
        }
//        exit(1);
        // Rename sheet
        $objPHPExcel->getActiveSheet()->setTitle('Simple');

        // Set active sheet index to the first sheet, 
        // so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);

        // Redirect output to a client’s web browser (Excel2007)

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $title . '.xls"');
        header('Cache-Control: max-age=0');

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        Yii::app()->end();

        // 
        // Once we have finished using the library, give back the 
        // power to Yii... 
        spl_autoload_register(array('YiiBase', 'autoload'));
    }
    
    public function actionReport1form(){            
        $this->render('report1form',array(
//			'model'=>$model,
		));
        }
    

    public function actionReport1() {
        $distributiondate=isset($_REQUEST['distributiondate']) ? date('Y-m-d', CDateTimeParser::parse($_REQUEST['distributiondate'], 'dd/MM/yyyy')) : time();
        $idfinancialyear = Yii::app()->session['idfinancialyear'];
        
//        $sql = "SELECT blockname, societyname, societyassistant, assistantcontact, groupname, demand, availability, distribution, balance FROM ";
//        $sql .= "( ";
//        $sql .= "SELECT *, ";
//        $sql .= "( ";
//        $sql .= "SELECT sum(demandquantity)   FROM `yii_demand` d, yii_stockitem si ";
//        $sql .= "WHERE d.idstockitem=si.idstockitem And idsociety=t2.idsociety And idstockgroup=t2.idstockgroup  And idstockgroup=t2.idstockgroup And idfinancialyear=2 ";
//        $sql .= ") demand, ";
//        $sql .= "( ";
//        $sql .= "SELECT sum(availabilityquantity) FROM `yii_distribution` d, yii_stockitem si ";
//        $sql .= "WHERE d.idstockitem=si.idstockitem And idsociety=t2.idsociety And idstockgroup=t2.idstockgroup  And idstockgroup=t2.idstockgroup And idfinancialyear=2 And distributiondate='$distributiondate' ";
//        $sql .= ") availability, ";
//        $sql .= "( ";
//        $sql .= "SELECT sum(distributionquantity) FROM `yii_distribution` d, yii_stockitem si ";
//        $sql .= "WHERE d.idstockitem=si.idstockitem And idsociety=t2.idsociety And idstockgroup=t2.idstockgroup  And idstockgroup=t2.idstockgroup And idfinancialyear=2 And distributiondate='$distributiondate' ";
//        $sql .= ") distribution, ";
//        $sql .= "( ";
//        $sql .= "SELECT sum(availabilityquantity)-sum(distributionquantity) FROM `yii_distribution` d, yii_stockitem si ";
//        $sql .= "WHERE d.idstockitem=si.idstockitem And idsociety=t2.idsociety  And idstockgroup=t2.idstockgroup And idfinancialyear=2 And idstockgroup=t2.idstockgroup And distributiondate='$distributiondate' ";
//        $sql .= ") balance ";
//        $sql .= "FROM  ";
//        $sql .= "( ";
//        $sql .= "SELECT * FROM yii_stockgroup sg,  ";
//        $sql .= "(SELECT b.blockname, societyname, idsociety, societyassistant, assistantcontact  FROM `yii_society` s, yii_block b ";
//        $sql .= "WHERE s.idblock=b.idblock ";
//        $sql .= "order by b.idblock) t1  ";
//        $sql .= "WHERE idstockgroup > 1 ";
//        $sql .= "order by idsociety ";
//        $sql .= ")t2 ";
//        $sql .= ")t3 ";


        $sql = "SELECT blockname, societyname, societyassistant, assistantcontact, groupname, demand, availability, distribution, balance FROM ";
        $sql .= "( ";
        $sql .= "SELECT *,  ";
        $sql .= "( ";
        $sql .= "SELECT sum(demandquantity)   FROM {{demand}} d, {{stockitem}} si ";
        $sql .= "WHERE d.idstockitem=si.idstockitem And idsociety=t2.idsociety And idstockgroup=t2.idstockgroup  And idstockgroup=t2.idstockgroup And idfinancialyear=$idfinancialyear ";
        $sql .= ") demand, ";
        $sql .= "( ";
        $sql .= "SELECT sum(availabilityquantity) FROM {{distribution}} d, {{stockitem}} si ";
        $sql .= "WHERE d.idstockitem=si.idstockitem And idsociety=t2.idsociety And idstockgroup=t2.idstockgroup  And idstockgroup=t2.idstockgroup And idfinancialyear=$idfinancialyear And distributiondate='$distributiondate' ";
        $sql .= ") availability, ";
        $sql .= "( ";
        $sql .= "SELECT sum(distributionquantity) FROM {{distribution}} d, {{stockitem}} si ";
        $sql .= "WHERE d.idstockitem=si.idstockitem And idsociety=t2.idsociety And idstockgroup=t2.idstockgroup  And idstockgroup=t2.idstockgroup And idfinancialyear=$idfinancialyear And distributiondate='$distributiondate' ";
        $sql .= ") distribution, ";
        $sql .= "( ";
        $sql .= "SELECT sum(availabilityquantity)-sum(distributionquantity) FROM {{distribution}} d, {{stockitem}} si ";
        $sql .= "WHERE d.idstockitem=si.idstockitem And idsociety=t2.idsociety  And idstockgroup=t2.idstockgroup And idfinancialyear=$idfinancialyear And idstockgroup=t2.idstockgroup And distributiondate='$distributiondate' ";
        $sql .= ") balance ";
        $sql .= "FROM  ";
        $sql .= "( ";
        $sql .= "SELECT * FROM yii_stockgroup sg,  ";
        $sql .= "(SELECT b.blockname, societyname, idsociety, societyassistant, assistantcontact  FROM {{society}} s, {{block}} b ";
        $sql .= "WHERE s.idblock=b.idblock ";
        $sql .= "order by b.idblock) t1  ";
        $sql .= "WHERE idstockgroup > 1 ";
        $sql .= "order by idsociety ";
        $sql .= ")t2 ";
        $sql .= ")t3 ";

        $connection = Yii::app()->db;
        $command = $connection->createCommand($sql);
        $dataReader = $command->query();


        $title = "Adaan Samiksha Report";
        //
        // get a reference to the path of PHPExcel classes 
        $phpExcelPath = Yii::getPathOfAlias('ext.phpexcel.Classes');

        // Turn off our amazing library autoload 
        spl_autoload_unregister(array('YiiBase', 'autoload'));

        //
        // making use of our reference, include the main class
        // when we do this, phpExcel has its own autoload registration
        // procedure (PHPExcel_Autoloader::Register();)
        include($phpExcelPath . DIRECTORY_SEPARATOR . 'PHPExcel.php');

        // Create new PHPExcel object
        $objPHPExcel = new PHPExcel();

        // Set properties
        $objPHPExcel->getProperties()->setCreator("ICTSoft")
                ->setLastModifiedBy("ICTSoft")
                ->setTitle($title)
                ->setSubject("ICTSoft Document")
                ->setDescription("xls generated using PHP classes.")
                ->setKeywords("xls php")
                ->setCategory("ICTSoft");

        // Add some data
//        $objPHPExcel->setActiveSheetIndex(0)
//                ->setCellValue('A1', 'Hello')
//                ->setCellValue('B2', 'world!')
//                ->setCellValue('C1', 'Hello')
//                ->setCellValue('D2', 'world!');
//
//        // Miscellaneous glyphs, UTF-8
//        $objPHPExcel->setActiveSheetIndex(0)
//                ->setCellValue('A4', 'Miscellaneous glyphs')
//                ->setCellValue('A5', 'éàèùâêîôûëïüÿäöüç');

        $styleArray = array(
            'font' => array(
                'bold' => true,
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
                'top' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                ),
                'bottom' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                ),
                'left' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                ),
                'right' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                ),
                'allborders' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                ),
            ),
            'fill' => array(
                'type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
                'rotation' => 90,
                'startcolor' => array(
                    'argb' => 'FFA0A0A0',
                ),
                'endcolor' => array(
                    'argb' => 'FFFFFFFF',
                ),
            ),
        );
        $styleArrayNormal = array(
            'font' => array(
                'bold' => false,
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
                'top' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                ),
                'bottom' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                ),
                'left' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                ),
                'right' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                ),
                'allborders' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                ),
            ),
            'fill' => array(
                'type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
                'rotation' => 90,
                'startcolor' => array(
                    'argb' => 'FFA0A0A0',
                ),
                'endcolor' => array(
                    'argb' => 'FFFFFFFF',
                ),
            ),
        );


        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->getStyle('G')->getAlignment()->setWrapText(true);


        $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A1', 'जिला सहकारी केन्द्रीय बैंक मर्या. बैतूल (म.प्र.)')
        ;
        $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A2', "रवी 2011-12 में कृषि आदान समीक्षा दिनांक $distributiondate की स्थिति")
        ;
        $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray(array(
            'font' => array(
                'bold' => true,
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            ));
        $objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray(array(
            'font' => array(
                'bold' => true,
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            ));
        
        $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A3', 'क्रं.')
                ->setCellValue('B3', 'विकास खंड का नाम')
                ->setCellValue('C3', 'नाम समिति')
                ->setCellValue('D3', 'संस्था सहायक का नाम')
                ->setCellValue('E3', 'संपर्क नंबर')
                ->setCellValue('F3', 'ग्रुप')
                ->setCellValue('G3', 'मांग')
                ->setCellValue('H3', 'उपलब्धता')
                ->setCellValue('I3', 'वितरण')
                ->setCellValue('J3', 'शेष')
        ;

        $objPHPExcel->getActiveSheet()->getStyle('A3:J3')->applyFromArray($styleArray);

        $index = 4;
        foreach ($dataReader as $row) {

            $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A' . $index, ($index - 1) . '')
                    ->setCellValue('B' . $index, $row['blockname'] . '')
                    ->setCellValue('C' . $index, $row['societyname'] . '')
                    ->setCellValue('D' . $index, $row['societyassistant'] . '')
                    ->setCellValue('E' . $index, $row['assistantcontact'] . '')
                    ->setCellValue('F' . $index, $row['groupname'] . ' ')
                    ->setCellValue('G' . $index, $row['demand'])
                    ->setCellValue('H' . $index, $row['availability'])
                    ->setCellValue('I' . $index, $row['distribution'])
                    ->setCellValue('J' . $index, $row['balance'])
            ;
            $objPHPExcel->getActiveSheet()->getStyle("A$index:J$index")->applyFromArray($styleArrayNormal);
            $index++;
        }



//        foreach ($dataReader as $row) {
//
//            $objPHPExcel->setActiveSheetIndex(0)
//                    ->setCellValue('A' . $index, $row['idcrf'] . '')
//                    ->setCellValue('B' . $index, $row['invoiceno'] . '')
//                    ->setCellValue('C' . $index, $row['customername'] . '')
//                    ->setCellValue('D' . $index, $row['plotno'] . ', ' . $row['colony'] . ', ' . $row['address'] . ', ' . $row['city'])
//                    ->setCellValue('E' . $index, $row['contactno1'] . ', ' . $row['contactno2'])
//                    ->setCellValue('F' . $index, $row['amtpaid'])
//                    ->setCellValue('G' . $index, $row['amtunpaid'])
//            ;
//            $objPHPExcel->getActiveSheet()->getStyle("A$index:G$index")->applyFromArray($styleArrayNormal);
//            $index++;
//        }
//        
//        exit(1);
//        foreach ($registrationmasters as $key => $value) {
//            $objPHPExcel->setActiveSheetIndex(0)
//                    ->setCellValue('A' . ($key + 1), $value->idregistrationmaster);
//        }
//        exit(1);
        // Rename sheet
        $objPHPExcel->getActiveSheet()->setTitle('Simple');
        $objPHPExcel->getActiveSheet()->mergeCells('A1:J1');
        $objPHPExcel->getActiveSheet()->mergeCells('A2:J2');

        // Set active sheet index to the first sheet, 
        // so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);

        // Redirect output to a client’s web browser (Excel2007)

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $title . '.xls"');
        header('Cache-Control: max-age=0');

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        Yii::app()->end();

        // 
        // Once we have finished using the library, give back the 
        // power to Yii... 
        spl_autoload_register(array('YiiBase', 'autoload'));
    }

}

?>
